package com.miage.m1.xml;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.miage.m1.xml.bd.ConfigConnection;
import com.miage.m1.xml.interfaces.IClubRepository;

public class ClubRepositoryMySQL implements IClubRepository {


	@Override
	public List<Club> findAll() throws SQLException, IOException, ClassNotFoundException{
			List<Club> clubs = new ArrayList<Club>();
			List<Player> players = new ArrayList<Player>();
			ResultSet resultPlayers;
			String queryPlayers;
			
			
			ResultSet resultClubs = query("SELECT * FROM Club, Person p1, Person p2 WHERE Club.idPresident=p1.idPerson AND Club.idCoach=p2.idPerson");
			
			while(resultClubs.next()){
queryPlayers=
						"SELECT Player.idPlayer, Player.number, Player.height, Player.weight, Person.pay, Player.position, Person.nationality, Person.birthday, Person.phone, Person.lastName, Person.firstName "+
						"FROM ClubPlayers, Player, Person "+
						"WHERE ClubPlayers.idPlayer = Player.idPlayer "+
						"AND Player.idPerson = Person.idPerson "+
						"AND ClubPlayers.idClub = "+resultClubs.getInt("idClub");
						resultPlayers = query(queryPlayers);
						while(resultPlayers.next()){
							players.add(
									new Player(
											resultPlayers.getInt("idPlayer"),
											resultPlayers.getString("lastName"),
											resultPlayers.getString("firstName"),
											resultPlayers.getString("nationality"),
											(Date) resultPlayers.getTimestamp("birthday"),
											resultPlayers.getInt("height"),
											resultPlayers.getInt("weight"),
											resultPlayers.getInt("number"),
											resultPlayers.getString("position"),
											resultPlayers.getString("phone"),
											resultPlayers.getDouble("pay")								
											)
									);
						}
						clubs.add(
						new Club(
								resultClubs.getInt("idClub"),
								resultClubs.getString("name"),
								resultClubs.getInt("establishment"),
								new Person(resultClubs.getInt("p1.idPerson"),resultClubs.getString("p1.firstName"), resultClubs.getString("p1.lastName"), resultClubs.getDate("p1.birthday")),
								new Person(resultClubs.getInt("p2.idPerson"),resultClubs.getString("p2.firstName"), resultClubs.getString("p2.lastName"), resultClubs.getDate("p2.birthday")),
								players
								)
						);
			}

			return clubs;

		}

	public ResultSet query(String requete) throws SQLException, IOException, ClassNotFoundException{
		Connection connection = ConfigConnection.getConnection("connexionMySQL.properties");
		Statement statement = connection.createStatement();
		return statement.executeQuery(requete);
	}

}
